<?php
class Model_UserAdmin extends Model_User
{
	public function getUser($id)
	{
    	$select  = $this->db->select()->from('user')->where('id = ?', (int)$id);
    	$user = $this->db->fetchRow($select);
    	//App_Log::query($this->db);
    	if (!$user) {
      		throw new Exception("user [$id] not found!");
    	}
    	return $user;
	}
	
	public function getAllUser()
	{		
		$select  = $this->db->select()->from('user');
		//$select->where('status = ?', 'Active'); //no status field
		$select->order('id asc');
		
		$result = $this->db->fetchAll($select);
		//App_Log::query($this->db);

		return $result;
	}
	
	
	public function addNews($data)
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
		
		$data_news = array(
            'news_content' => $data['news_content'],
            'max_display_times' => $data['max_display_times'],
            'from_date' => $data['from_date'],
            'end_date' => $data['end_date'],
			'create_time' => $now
		);
		
		$this->db->insert('acc_news',$data_news);
		$news_id = $this->db->lastInsertId();
		
		if ($news_id){
			$data_service = array(
	            'news_id' => $news_id,
	            'dpt_id' => $data['dpt_id']
			);
			$this->db->insert('acc_news_service',$data_service);
			$data_location = array(
	            'news_id' => $news_id,
	            'country' => $data['country'],
	            'city' => $data['city'],
	            'state' => $data['state']
			);
			$this->db->insert('acc_news_location',$data_location);
			return $news_id;
		}else 
			return false;		
	}

	
	public function deleteNews($news_id){
		$sql = " update acc_news set status = 'Disable' where news_id='$news_id' ";
		$this->db->query($sql);	
		return true;	
	}
	
	public function updateNews($news_id,$data)
	{
		$where = array('news_id = ?' => (int)$news_id);
		$data_news = array(
            'news_content' => $data['news_content'],
            'max_display_times' => $data['max_display_times'],
            'from_date' => $data['from_date'],
            'end_date' => $data['end_date']
		);
		
		$num1 = $this->db->update('acc_news', $data_news, $where);
		
		$updated1 = array('affected'=>$num1);
		
		$data_location = array(
            'country' => $data['country'],
            'city' => $data['city'],
            'state' => $data['state']
		);
		
		$num2 = $this->db->update('acc_news_location', $data_location, $where);
		$updated2 = array('affected'=>$num2);
		
		$data_service = array(
            'dpt_id' => $data['dpt_id']
		);
		
		$num3 = $this->db->update('acc_news_service', $data_service, $where);
		$updated3 = array('affected'=>$num3);
				
		if ($updated1 or $updated2 or $updated3){
			return true;
		}else 
			return false;
	}	
	
	public function getNews($id)
	{
    	$select  = $this->db->select()->from('acc_news')->where('news_id = ?', (int)$id);
    	$news = $this->db->fetchRow($select);
    	//App_Log::query($this->db);
    	if (!$news) {
    		return false;
    	}
    	
    	$select  = $this->db->select()->from('acc_news_location')->where('news_id = ?', (int)$id);
    	$news_location = $this->db->fetchRow($select);
    	$news['country'] = $news_location['country'];
    	$news['city'] = $news_location['city'];
    	$news['state'] = $news_location['state'];

    	$select  = $this->db->select()->from('acc_news_service')->where('news_id = ?', (int)$id);
    	$news_service = $this->db->fetchRow($select);
    	$news['dpt_id'] = $news_service['dpt_id'];
    	$news['news_content']=stripslashes($news['news_content']);
    	
    	return $news;
	}
	
	public function getAllNews($start,$limit)
	{		
		$select  = $this->db->select()->from('acc_news');
		$select->where('status = ?', 'Active'); //no status field
		$select->where('type = ?', 'News'); //no status field
		$select->order('news_id desc');
		$select->limit($limit,$start);

		//echo $select; exit;
		$result = $this->db->fetchAll($select);
		
		$news=array();
		$news['total']=count($result);
		$news['data']=$result;
		return $news;
	}

	public function getLastNews($client_id)
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
		
		//get client's subscribed services
		$select  = $this->db->select('dpt_id')->from('acc_client_service');
		$select->where('client_id = ?', "$client_id"); 
		$services = $this->db->fetchAll($select);
		//Zend_Debug::dump($services);
		
		$service_str = '';
		foreach($services as $key=>$value){
			$service_str .= $value['dpt_id'].',';
		}
		$service_str = substr($service_str,0,strlen($service_str)-1);
		
		
		//get client's profile information
		$select  = $this->db->select()->from('acc_client_profile');
		$select->where('client_id = ?', "$client_id"); 
		$profile = $this->db->fetchAll($select);
		
		
		$select  = $this->db->select()->from(array('a'=>'acc_news'));
        $select->joinLeft(array('s'=>'acc_news_service'),'a.news_id=s.news_id');
        $select->joinLeft(array('l'=>'acc_news_location'),'a.news_id=l.news_id');
        $select->where('status = ?', 'Active'); 
        
        if (count($services)>0){
        	$select->where("s.dpt_id in ( $service_str )"); 
        }
        
        if (!empty($profile['country'])){
        	$select->where('l.country = ?', $profile['country']);      	
        }
        
        if (!empty($profile['city'])){
        	$select->where('l.city = ?', $profile['city']);   	
        }

        if (!empty($profile['state'])){
        	$select->where('l.state = ?', $profile['state']);      	
        }

        $select->where("(a.from_date = '0000-00-00 00:00:00' and a.end_date='0000-00-00 00:00:00') or ( a.from_date < '$now' and a.end_date='0000-00-00 00:00:00') or ( a.from_date < '$now' and a.end_date> '$now' ) ");      	
        $select->where("a.max_display_times = '' or ( a.max_display_times != '' and a.display_times < max_display_times) ");      	
        
		$select->order('RAND()')->limit(1);
        
		//echo $select; exit;
		$news = $this->db->fetchAll($select);
		
		if (!empty($news)){
    		$news[0]['news_content']=stripslashes($news[0]['news_content']);
			$news_id = $news[0]['news_id'];
			$sql = " update acc_news set display_times = display_times+1 where news_id='$news_id' ";
			$this->db->query($sql);
		}
		
		return $news;
	}

	
	public function addMessage($data)
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
		
		$data_news = array(
			'type'=>'Message',
			'icon'=>$data['icon'],
            'news_content' => $data['news_content'],
            'max_display_times' => $data['max_display_times'],
            'from_date' => $data['from_date'],
            'end_date' => $data['end_date'],
            'important' => $data['important'],
			'create_time' => $now
		);
		
		$this->db->insert('acc_news',$data_news);
		$news_id = $this->db->lastInsertId();
		
		if ($news_id){
			$data_service = array(
	            'news_id' => $news_id,
	            'dpt_id' => $data['dpt_id']
			);
			$this->db->insert('acc_news_service',$data_service);
			return $news_id;
		}else 
			return false;		
	}

	
	public function deleteMessage($news_id){
		$sql = " update acc_news set status = 'Disable' where news_id='$news_id' ";
		$this->db->query($sql);	
		return true;	
	}
	
	public function updateMessage($news_id,$data)
	{
		$where = array('news_id = ?' => (int)$news_id);
		$data_news = array(
			'icon'=>$data['icon'],
			'news_content' => $data['news_content'],
            'max_display_times' => $data['max_display_times'],
            'from_date' => $data['from_date'],
            'end_date' => $data['end_date'],
            'important' => $data['important']
		);
		
		$num1 = $this->db->update('acc_news', $data_news, $where);
		
		$updated1 = array('affected'=>$num1);
		
		$data_service = array(
            'dpt_id' => $data['dpt_id']
		);
		
		$num3 = $this->db->update('acc_news_service', $data_service, $where);
		$updated3 = array('affected'=>$num3);
				
		if ($updated1 or $updated3){
			return true;
		}else 
			return false;
	}	
	

	public function getMessage($id)
	{
    	$select  = $this->db->select()->from('acc_news')->where('news_id = ?', (int)$id);
    	$news = $this->db->fetchRow($select);
    	//App_Log::query($this->db);
    	if (!$news) {
    		return false;
    	}
    	
    	$select  = $this->db->select()->from('acc_news_service')->where('news_id = ?', (int)$id);
    	$news_service = $this->db->fetchRow($select);
    	$news['dpt_id'] = $news_service['dpt_id'];
    	$news['news_content']=stripslashes($news['news_content']);
    	
    	return $news;
	}
	
	public function getAllMessages($start,$limit)
	{		
		$select  = $this->db->select()->from('acc_news');
		$select->where('status = ?', 'Active'); //no status field
		$select->where('type = ?', 'Message'); //no status field
		$select->order('news_id desc');
		$select->limit($limit,$start);

		//echo $select; exit;
		$result = $this->db->fetchAll($select);
		
		$news=array();
		$news['total']=count($result);
		$news['data']=$result;
		return $news;
	}
	
	public function udpatePassword($email,$password)
	{
	    $select  = $this->db->select()->from('user')->where('email = ?', $email);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$salt = strtolower(uniqid(rand(), true));
	    $hashed_pwd = strtolower(md5($password . $salt));
		$sql = " update user set password='$hashed_pwd',salt='$salt' where email='$email' ";
		$this->db->query($sql);
		return true;
	}	
	
	
}